/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package application.dao;

import application.model.EntitySearchResults;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 *
 * @author Ajdin Brandic
 */
@Repository("entitySearchResultsDao")
public class EntitySearchResultsDaoImpl implements EntitySearchResultsDao {

    private String userInputSearchTerm1 = "";
    private String userInputSearchTerm2 = "";
    private String userInputSearchOperator = "";
    private String userInputSearchTypeRadioB = "";
    private String userInputSearchMatchLimit = "";
    @Autowired
    private SessionFactory sessionFactory;    

    // To get list of all articles
    @SuppressWarnings("unchecked")
    public List<EntitySearchResults> listSelectedEntitySearchResults(String userInputString) {

        //TODO: Somehow get Connection since sessionFactory.getCurrentSession().connection() is deprecated
        //TODO: Need this to use PreparedStatement which gards against SQL Injection
        /*        
        String selectStatement = "SELECT * FROM User WHERE userId = ? ";
        PreparedStatement prepStmt = sessionFactory.getCurrentSession().connection().prepareStatement(selectStatement);
        prepStmt.setString(1, userInputHasMap);
        ResultSet rs = prepStmt.executeQuery();
         */

        List<EntitySearchResults> selectedEntityAccompsCont = new ArrayList<EntitySearchResults>();

        // Regular Expression MySQL5 (SELECT 'pip' REGEXP '[[:<:]]pi[[:>:]]') gets all 'pi' where predecessor or successor char
        // is not an alpha char (i.e. -pi = OK, pi-OK, pip = NOT OK);

        String searchQuery = "select `ea`.`entityID` as entityID, "
                + "`ea`.`accompTypeID` as accompTypeID, "
                + "`ea`.`title` as accompTitle, "
                + "`ea`.`description` as accompDescription, "
                //+ "`ea`.`priority` as accompPriority, "
                + "`ea`.`entityAccomplishmentID` as entityAccomplishmentID, "
                + "`c`.`email` as entityEmail,  "
                + "`at`.`accompTypeDescription` as accompTypeDescription, "
                + "`at`.`weightOfType` as accompTypeWeight, "
                + "`me`.`fname` as entityFname, "
                + "`me`.`mname` as entityMname, "
                + "`me`.`sname` as entitySname, "
                + "`me`.`active` as entityActive, "
                + "`et`.`description` as entityTypeDescription "
                + "FROM entityaccomplishment ea "
                + "LEFT JOIN mainentity me on ea.entityID=me.entityID "
                + "LEFT JOIN contactdetails c on ea.entityID=c.entityID "
                + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                + "LEFT JOIN accomplishmenttype at on ea.accompTypeID=at.accompTypeID "
                + "WHERE (ea.title " + createWherePartRegexLike(userInputString.replace("'", "")) + ")";
        //+ "WHERE (ea.title REGEXP '[[:<:]]"+userInputString.replace("'", "")+"[[:>:]]') ";
        //+ "GROUP BY  `ea`.`entityID`";

        selectedEntityAccompsCont = sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntitySearchResults.class).list();
        
        //System.out.println("Q1::::::");
        
        //return selectedEntityAccompsCont;
        return this.javaRejExprCompare(userInputString, selectedEntityAccompsCont);
    }

    // To get list of all entityAccomplishments
    @SuppressWarnings("unchecked")
    public List<EntitySearchResults> listOfEntityAccomplishmentsByKeyword(String userInputString, String excludeEntityID) {

        //TODO: Hibernate bug prvents content of regexp to contain ' . REGEXP '[[:<:]]Ajdin's work[[:>:]]').http://opensource.atlassian.com/projects/hibernate/browse/HHH-2697
        //TODO: Somehow get Connection since sessionFactory.getCurrentSession().connection() is deprecated
        //TODO: Need this to use PreparedStatement which gards against SQL Injection
        /*
        String selectStatement = "SELECT * FROM User WHERE userId = ? ";
        PreparedStatement prepStmt = sessionFactory.getCurrentSession().connection().prepareStatement(selectStatement);
        prepStmt.setString(1, userInputHasMap);
        ResultSet rs = prepStmt.executeQuery();
         */

        if(userInputString.trim().equals("") || userInputString.length()<3) {
            List selectedEntityAccompsCont2 = new ArrayList<EntitySearchResults>();
            return selectedEntityAccompsCont2;
        } else {
        List<EntitySearchResults> selectedEntityAccompsCont = new ArrayList<EntitySearchResults>();

        //validate input

        // Regular Expression MySQL5 (SELECT 'pip' REGEXP '[[:<:]]pi[[:>:]]') gets all 'pi' where predecessor or successor char
        // is not an alpha char (i.e. -pi = OK, pi-OK, pip = NOT OK);

        userInputString = userInputString.replace("'", "");
        String searchQuery = "select `ea`.`entityID` as entityID, "
                + "`ea`.`accompTypeID` as accompTypeID, "
                + "`ea`.`title` as accompTitle, "
                + "`ea`.`description` as accompDescription, "
                //+ "`ea`.`priority` as accompPriority, "
                + "`ea`.`entityAccomplishmentID` as entityAccomplishmentID, "
                + "`c`.`email` as entityEmail,  "
                + "`at`.`accompTypeDescription` as accompTypeDescription, "
                + "`at`.`weightOfType` as accompTypeWeight, "
                + "`me`.`fname` as entityFname, "
                + "`me`.`mname` as entityMname, "
                + "`me`.`sname` as entitySname, "
                + "`me`.`active` as entityActive, "
                + "`et`.`description` as entityTypeDescription "
                + "FROM entityaccomplishment ea "
                + "LEFT JOIN mainentity me on ea.entityID=me.entityID "
                + "LEFT JOIN contactdetails c on ea.entityID=c.entityID "
                + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                + "LEFT JOIN accomplishmenttype at on ea.accompTypeID=at.accompTypeID "
                + "WHERE (ea.title " + createWherePartRegexLike(userInputString.replace("'", "")) + ")"
                //+ "WHERE (ea.title REGEXP '[[:<:]]"+userInputString.trim()+"[[:>:]]') "
                + " AND ea.entityID != " + excludeEntityID + "; ";

        selectedEntityAccompsCont = sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntitySearchResults.class).list();
        

        /*
        //test regexp. move this out of here
        String patternStr = "\\b" + userInputString.toUpperCase() + "\\b";
        Pattern pattern = Pattern.compile(patternStr);
        List selectedEntityAccompsCont2 = new ArrayList<EntitySearchResults>();
        for (int x = 0; selectedEntityAccompsCont.size() > x; x++) {
            EntitySearchResults tmpEnt = (EntitySearchResults) selectedEntityAccompsCont.get(x);
            Matcher matcher = pattern.matcher(tmpEnt.getAccompTitle().toUpperCase());
            boolean matchFound = matcher.find();
            if (matchFound == true) {
                //add to return array
                selectedEntityAccompsCont2.add(tmpEnt);
            }
        }*/
        
//System.out.println("Q2::::::"+selectedEntityAccompsCont2.size()+" ==== "+selectedEntityAccompsCont.size());
        return this.javaRejExprCompare(userInputString, selectedEntityAccompsCont);
        }
    }

    // To get list of all articles
    //Input values must be validated prior to execution of this code
    @SuppressWarnings("unchecked")
    public List<EntitySearchResults> listSelectedEntitySearchResults(HashMap<String, String> userInputHashMap) {

        //TODO: Somehow get Connection since sessionFactory.getCurrentSession().connection() is deprecated
        //TODO: Need this to use PreparedStatement which gards against SQL Injection
        /*
        String selectStatement = "SELECT * FROM User WHERE userId = ? ";
        PreparedStatement prepStmt = sessionFactory.getCurrentSession().connection().prepareStatement(selectStatement);
        prepStmt.setString(1, userInputHasMap);
        ResultSet rs = prepStmt.executeQuery();
         */

        List<EntitySearchResults> selectedEntityAccompsCont = new ArrayList<EntitySearchResults>();
        ArrayList<String> allSearchTerms1 = new ArrayList<String>(); //holds all search terms linked to term1 (inc synonyms)
        ArrayList<String> allSearchTerms2 = new ArrayList<String>(); //holds all search terms linked to term2 (inc synonyms)

        //add all term1 search terms (inc. searchTerm1 field and any synonyms) to an array
        allSearchTerms1.add(userInputHashMap.get("searchTerm1"));
        if(userInputHashMap.get("searchTerm1Synonyms") != null) {
            if (userInputHashMap.get("searchTerm1Synonyms").trim().length() > 1) {
                String[] synonyms1 = userInputHashMap.get("searchTerm1Synonyms").split(";;");
                for (int i = 0; synonyms1.length > i; i++) {
                    allSearchTerms1.add(synonyms1[i]);
                }
            }
        }

        //add all term2 search terms (inc. searchTerm2 field and any synonyms) to an array
        allSearchTerms2.add(userInputHashMap.get("searchTerm2"));
        if(userInputHashMap.get("searchTerm2Synonyms") != null) {
            if (userInputHashMap.get("searchTerm2Synonyms").trim().length() > 1) {
                String[] synonyms2 = userInputHashMap.get("searchTerm2Synonyms").split(";;");
                for (int j = 0; synonyms2.length > j; j++) {
                    allSearchTerms2.add(synonyms2[j]);
                }
            }
        }

//System.out.println("allSearchTerms1: "+allSearchTerms1.toString());
//System.out.println("allSearchTerms2: "+allSearchTerms2.toString());

        /*
         * searchTerm1", searchOperator", searchTerm2", searchType"
         */        

        if (userInputHashMap.get("searchOperator").toLowerCase().equals("not") && this.isNotEmpty(userInputHashMap.get("searchTerm1")) && this.isNotEmpty(userInputHashMap.get("searchTerm2"))) {
            //this.userInputSearchTerm1 = userInputHashMap.get("searchTerm1");
            //this.userInputSearchTerm2 = userInputHashMap.get("searchTerm2");

            // Regular Expression MySQL5 (SELECT 'pip' REGEXP '[[:<:]]pi[[:>:]]') gets all 'pi' where predecessor or successor char
            // is not an alpha char (i.e. -pi = OK, pi-OK, pip = NOT OK);

            String searchQuery = "";

            //check if synonyms1 exist
            if (allSearchTerms1.size() >= 1) {

                for (int x = 0; allSearchTerms1.size() > x; x++) {

                    if (allSearchTerms2.size() >= 1) {

                        for (int y = 0; allSearchTerms2.size() > y; y++) {
                            searchQuery = "select `ea`.`entityID` as entityID, "
                                    + "`ea`.`accompTypeID` as accompTypeID, "
                                    + "`ea`.`title` as accompTitle, "
                                    + "`ea`.`description` as accompDescription, "
                                    //+ "`ea`.`priority` as accompPriority, "
                                    + "`ea`.`entityAccomplishmentID` as entityAccomplishmentID, "
                                    + "`c`.`email` as entityEmail,  "
                                    + "`at`.`accompTypeDescription` as accompTypeDescription, "
                                    + "`at`.`weightOfType` as accompTypeWeight, "
                                    + "`me`.`fname` as entityFname, "
                                    + "`me`.`mname` as entityMname, "
                                    + "`me`.`sname` as entitySname, "
                                    + "`me`.`active` as entityActive, "
                                    + "`et`.`description` as entityTypeDescription "
                                    + "FROM entityaccomplishment ea "
                                    + "LEFT JOIN mainentity me on ea.entityID=me.entityID "
                                    + "LEFT JOIN contactdetails c on ea.entityID=c.entityID "
                                    + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                                    + "LEFT JOIN accomplishmenttype at on ea.accompTypeID=at.accompTypeID "
                                    + "WHERE (ea.title REGEXP '[[:<:]]" + allSearchTerms1.get(x).replace("'", "") + "[[:>:]]' AND NOT ea.title REGEXP '[[:<:]]" + allSearchTerms2.get(y).replace("'", "") + "[[:>:]]') ";

                            selectedEntityAccompsCont.addAll(sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntitySearchResults.class).list());                            

                        }
                    }

                }
            }            

            //TODO: Add synonym search for NOT
        } else if (userInputHashMap.get("searchOperator").toLowerCase().equals("and") && this.isNotEmpty(userInputHashMap.get("searchTerm1")) && this.isNotEmpty(userInputHashMap.get("searchTerm2"))) {
            //this.userInputSearchTerm1 = userInputHashMap.get("searchTerm1");
            //this.userInputSearchTerm2 = userInputHashMap.get("searchTerm2");
            String searchQuery = "";

            //check if synonyms1 exist
            if (allSearchTerms1.size() >= 1) {
                for (int x = 0; allSearchTerms1.size() > x; x++) {
                    if (allSearchTerms2.size() >= 1) {
                        for (int y = 0; allSearchTerms2.size() > y; y++) {
                            searchQuery = "select `ea`.`entityID` as entityID, "
                                    + "`ea`.`accompTypeID` as accompTypeID, "
                                    + "`ea`.`title` as accompTitle, "
                                    + "`ea`.`description` as accompDescription, "
                                    //+ "`ea`.`priority` as accompPriority, "
                                    + "`ea`.`entityAccomplishmentID` as entityAccomplishmentID, "
                                    + "`c`.`email` as entityEmail,  "
                                    + "`at`.`accompTypeDescription` as accompTypeDescription, "
                                    + "`at`.`weightOfType` as accompTypeWeight, "
                                    + "`me`.`fname` as entityFname, "
                                    + "`me`.`mname` as entityMname, "
                                    + "`me`.`sname` as entitySname, "
                                    + "`me`.`active` as entityActive, "
                                    + "`et`.`description` as entityTypeDescription "
                                    + "FROM entityaccomplishment ea "
                                    + "LEFT JOIN mainentity me on ea.entityID=me.entityID "
                                    + "LEFT JOIN contactdetails c on ea.entityID=c.entityID "
                                    + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                                    + "LEFT JOIN accomplishmenttype at on ea.accompTypeID=at.accompTypeID "
                                    + "WHERE (ea.title REGEXP '[[:<:]]" + allSearchTerms1.get(x).replace("'", "") + "[[:>:]]' AND ea.title REGEXP '[[:<:]]" + allSearchTerms2.get(y).replace("'", "") + "[[:>:]]') ";

                            selectedEntityAccompsCont.addAll(sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntitySearchResults.class).list());

                        }
                    }

                }
            }

        } else {
            userInputHashMap.put("searchOperator", "None");
        }

        if (userInputHashMap.get("searchOperator").toLowerCase().equals("none") && this.isNotEmpty(userInputHashMap.get("searchTerm1"))) {
            this.userInputSearchTerm1 = userInputHashMap.get("searchTerm1");

            // Regular Expression MySQL5 (SELECT 'pip' REGEXP '[[:<:]]pi[[:>:]]') gets all 'pi' where predecessor or successor char
            // is not an alpha char (i.e. -pi = OK, pi-OK, pip = NOT OK);
            String searchQuery = "select `ea`.`entityID` as entityID, "
                    + "`ea`.`accompTypeID` as accompTypeID, "
                    + "`ea`.`title` as accompTitle, "
                    + "`ea`.`description` as accompDescription, "
                    //+ "`ea`.`priority` as accompPriority, "
                    + "`ea`.`entityAccomplishmentID` as entityAccomplishmentID, "
                    + "`c`.`email` as entityEmail,  "
                    + "`at`.`accompTypeDescription` as accompTypeDescription, "
                    + "`at`.`weightOfType` as accompTypeWeight, "
                    + "`me`.`fname` as entityFname, "
                    + "`me`.`mname` as entityMname, "
                    + "`me`.`sname` as entitySname, "
                    + "`me`.`active` as entityActive, "
                    + "`et`.`description` as entityTypeDescription "
                    + "FROM entityaccomplishment ea "
                    + "LEFT JOIN mainentity me on ea.entityID=me.entityID "
                    + "LEFT JOIN contactdetails c on ea.entityID=c.entityID "
                    + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                    + "LEFT JOIN accomplishmenttype at on ea.accompTypeID=at.accompTypeID "
                    + "WHERE ea.title REGEXP '[[:<:]]" + this.userInputSearchTerm1.replace("'", "") + "[[:>:]]' ";

            selectedEntityAccompsCont = sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntitySearchResults.class).list();            

            //check if synonyms exist
            if (userInputHashMap.get("searchTerm1Synonyms").trim().length() > 1) {
                String[] synonyms1 = userInputHashMap.get("searchTerm1Synonyms").split(";;");
                for (int i = 0; synonyms1.length > i; i++) {
                    searchQuery = "select `ea`.`entityID` as entityID, "
                            + "`ea`.`accompTypeID` as accompTypeID, "
                            + "`ea`.`title` as accompTitle, "
                            + "`ea`.`description` as accompDescription, "
                            //+ "`ea`.`priority` as accompPriority, "
                            + "`ea`.`entityAccomplishmentID` as entityAccomplishmentID, "
                            + "`c`.`email` as entityEmail,  "
                            + "`at`.`accompTypeDescription` as accompTypeDescription, "
                            + "`at`.`weightOfType` as accompTypeWeight, "
                            + "`me`.`fname` as entityFname, "
                            + "`me`.`mname` as entityMname, "
                            + "`me`.`sname` as entitySname, "
                            + "`me`.`active` as entityActive, "
                            + "`et`.`description` as entityTypeDescription "
                            + "FROM entityaccomplishment ea "
                            + "LEFT JOIN mainentity me on ea.entityID=me.entityID "
                            + "LEFT JOIN contactdetails c on ea.entityID=c.entityID "
                            + "LEFT JOIN entitytypes et on me.entityTypeID=et.entityTypeID "
                            + "LEFT JOIN accomplishmenttype at on ea.accompTypeID=at.accompTypeID "
                            + "WHERE ea.title REGEXP '[[:<:]]" + synonyms1[i].replace("'", "") + "[[:>:]]' ";

                    selectedEntityAccompsCont.addAll(sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntitySearchResults.class).list());                    

                }
            }

        }

        return selectedEntityAccompsCont;
    }

    // To get list of all articles for an entity by entityID
    //Input values must be validated prior to execution of this code
    @SuppressWarnings("unchecked")
    public List<EntitySearchResults> listSelectedEntityByIDResults(HashMap<String, String> userInputHashMap) {

        List<EntitySearchResults> selectedEntityAccompsCont = new ArrayList<EntitySearchResults>();

        this.userInputSearchTerm1 = userInputHashMap.get("searchEntityID");

        String searchQuery = "select `ea`.`entityID` as entityID, "
                + "`ea`.`accompTypeID` as accompTypeID, "
                + "`ea`.`title` as accompTitle, "
                + "`ea`.`description` as accompDescription, "
                + "`ea`.`priority` as accompPriority, "
                + "`ea`.`entityAccomplishmentID` as entityAccomplishmentID, "
                + "`c`.`email` as entityEmail,  "
                + "`me`.`fname` as entityFname, "
                + "`me`.`mname` as entityMname, "
                + "`me`.`sname` as entitySname, "
                + "`me`.`active` as entityActive, "
                + "`et`.`description` as entityTypeDescription "
                + "from entityaccomplishment ea, contactdetails c, mainentity me, entitytypes et "
                + "where me.entityID=" + this.userInputSearchTerm1.replace("'", "") + " "
                + "and ea.entityID=me.entityID and me.entityTypeID=et.entityTypeID ";

        selectedEntityAccompsCont = sessionFactory.getCurrentSession().createSQLQuery(searchQuery).addEntity(EntitySearchResults.class).list();        

        return selectedEntityAccompsCont;

    }

    /*Doing SQL REGEXP on long string is very slow. Long strings will use LIKE*/
    private String createWherePartRegexLike(String rawString) {
        String wherePart = "";
        /*
        //replace : with %.  Hibernate has a bug which ,makes query fail if search string contains :        
         */
        //wherePart = " REGEXP '[[:<:]]"+rawString.replace("'", "")+"[[:>:]]' ";
        wherePart = " LIKE \"%" + rawString.replace(":", "%").replace("\"", "").replace("?", "") + "%\"";
        return wherePart;
    }


    /*Make sure the input value is not empty
     */
    private Boolean isNotEmpty(String s) {
        Boolean isNotEmpty = false;

        if (s != null && s.trim().length() > 1) {
            isNotEmpty = true;
        }
        return isNotEmpty;
    }


    private List<EntitySearchResults> javaRejExprCompare(String userInputString, List<EntitySearchResults> selectedEntityAccompsCont) {
        //test regexp. move this out of here
        //Pattern doesn't like + in string or unclosed (
        if(userInputString.contains("+")) { userInputString=userInputString.replace("+", "pls"); }
        if(userInputString.contains("(")) { userInputString=userInputString.replace("(", " ").replace(")", " "); }
        if(userInputString.contains(")")) { userInputString=userInputString.replace("(", " ").replace(")", " "); }

        System.out.println("Search term: "+userInputString);
        String patternStr = "\\b" + userInputString.toUpperCase() + "\\b";
        Pattern pattern = Pattern.compile(patternStr);
        List selectedEntityAccompsCont2 = new ArrayList<EntitySearchResults>();
        for (int x = 0; selectedEntityAccompsCont.size() > x; x++) {
            EntitySearchResults tmpEnt = (EntitySearchResults) selectedEntityAccompsCont.get(x);
            Matcher matcher = pattern.matcher(tmpEnt.getAccompTitle().toUpperCase());
            boolean matchFound = matcher.find();
            if (matchFound == true) {
                //add to return array
                selectedEntityAccompsCont2.add(tmpEnt);
            }
        }

        return selectedEntityAccompsCont2;
    }
}
